package br.ufrj.neg.preparacao.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Hashtable;

import br.ufrj.neg.model.NegociacaoElementoModel;
import br.ufrj.neg.model.ServiceLocator;
import br.ufrj.neg.preparacao.bean.NegociacaoBean;
import br.ufrj.neg.util.Data;


public class NegociacaoModel {
	//private static Logger log = Logger.getLogger("rootLogger");
/**
 * Retorna o idNegocio. Caso falhe, retorna -1.
 * 
 * @param neg
 * @return
 */
	public int cadastraNegociacao(NegociacaoBean neg) {
		PreparedStatement ps=null;
		Connection conn = ServiceLocator.getConnection();
		try {
			String sql = "INSERT INTO negociacao("
					+ "NEGO_DS_NEGOCIACAO," 
					+ "NEGO_DS_CLIENTE,"
					+ "NEGO_DS_NEGOCIADOR," 
					+ "NEGO_DT_CADASTRO," +
					"NEGO_NR_VARIACAO," +
					"USUA_CD_ID," +
					"USUA_CD_ID_CONTRAPARTE," +
					"NEGO_CD_PASSO," +
					"MONE_CD_ID"
					+ ") values(" + "?," + "?," + "?,"
					+ "to_date(?,'dd/mm/yyyy'),?,?,?,?,?)";
			ps = conn.prepareStatement(sql);
			ps.setString(1, neg.getNegociacao());
			ps.setString(2, neg.getCliente());
			ps.setString(3, neg.getNegociador());
			ps.setString(4, neg.getDataCadastro().getDataBr());
			ps.setInt(5, 1);//seta a variação como 100%
			ps.setInt(6, neg.getIdUsuario());
			ps.setInt(7,neg.getIdUsuarioContraparte());
			ps.setInt(8, neg.getPasso());
			ps.setInt(9, neg.getIdModeloNegociacao());
			ps.executeUpdate();
			sql="SELECT max(nego_cd_id) as \"num\" FROM negociacao";
			ps = conn.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			conn.commit();
			int idNegocio = -1;
			while (rs.next()){
				idNegocio = rs.getInt("num");
			}
			NegociacaoElementoModel nem = new NegociacaoElementoModel();
			if (nem.criaNegociacaoElemento(idNegocio)){
				return idNegocio;
			}else{
				return -1;
			}
		} catch (Exception e) {
			System.out.println(e);
			return -1;
		}finally{
			try{ps.close();}catch(Exception e){System.out.println(e);}
			try{conn.close();}catch(Exception e){System.out.println(e);}
		}
	}
	
	public boolean atualizaNegociacao(NegociacaoBean neg){
		PreparedStatement ps = null;
		Connection conn = ServiceLocator.getConnection();
		try {
			String sql = "UPDATE NEGOCIACAO SET " +
					"NEGO_DS_NEGOCIACAO=?," + 
					"NEGO_DS_CLIENTE=?," + 
					"NEGO_DS_NEGOCIADOR=?," + 
					"NEGO_DT_CADASTRO=to_date(?,'dd/mm/yyyy')," +
					"NEGO_DT_ATUALIZACAO=to_date(?,'dd/mm/yyyy')," +
					"NEGO_NR_VARIACAO=?," +
					"NEGO_CD_TIPO_NEGOCIACAO=?," +
					"USUA_CD_ID=?," +
					"USUA_CD_ID_CONTRAPARTE=?," +
					"NEGO_CD_PASSO=?," +
					"MONE_CD_ID=?" +
					"WHERE NEGO_CD_ID=?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, neg.getNegociacao());
			ps.setString(2, neg.getCliente());
			ps.setString(3, neg.getNegociador());
			ps.setString(4, neg.getDataCadastro().getDataBr());
			ps.setString(5, neg.getDataAtualizacao().getDataBr());
			ps.setDouble(6, neg.getVariacao());
			ps.setInt(7, neg.getTipoNegociacao());
			ps.setInt(8, neg.getIdUsuario());
			ps.setInt(9, neg.getIdUsuarioContraparte());
			ps.setInt(10,neg.getPasso());
			ps.setInt(11, neg.getIdModeloNegociacao());
			ps.setInt(12, neg.getIdNegociacao());
			ps.executeUpdate();
			conn.commit();
			return true;
		} catch (Exception e) {
			System.out.println(e);
			return false;
		}finally{
			try{ps.close();}catch(Exception e){System.out.println(e);}
			try{conn.close();}catch(Exception e){System.out.println(e);}
		}
	}
	public NegociacaoBean getNegociacao(int idNegocio){
		ResultSet rs = null;
		PreparedStatement ps = null;
		Connection conn = ServiceLocator.getConnection();
		try{
			String sql = "SELECT * FROM NEGOCIACAO WHERE NEGO_CD_ID=?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, idNegocio);
			rs = ps.executeQuery();
			NegociacaoElementoModel nem = new NegociacaoElementoModel();
			NegociacaoBean neg = new NegociacaoBean();
			while(rs.next()){
				neg.setCliente(rs.getString("NEGO_DS_CLIENTE"));
				neg.setDataAtualizacao(new Data(rs.getDate("NEGO_DT_ATUALIZACAO")));
				neg.setDataCadastro(new Data(rs.getDate("NEGO_DT_CADASTRO")));
				neg.setIdNegociacao(rs.getInt("NEGO_CD_ID"));
				neg.setNegociacao(rs.getString("NEGO_DS_NEGOCIACAO"));
				neg.setNegociador(rs.getString("NEGO_DS_NEGOCIADOR"));
				neg.setTipoNegociacao(rs.getInt("NEGO_CD_TIPO_NEGOCIACAO"));
				neg.setVariacao(rs.getDouble("NEGO_NR_VARIACAO"));
				neg.setIdUsuario(rs.getInt("USUA_CD_ID"));
				neg.setIdUsuarioContraparte(rs.getInt("USUA_CD_ID_CONTRAPARTE"));
				neg.setPasso(rs.getInt("NEGO_CD_PASSO"));
				neg.setIdModeloNegociacao(rs.getInt("MONE_CD_ID"));
				neg.setElementos(nem.getNegociacaoElemento(rs.getInt("NEGO_CD_ID")));
			}
			return neg;
		}
		catch(Exception e){
			return null;		
		}finally{
			try{rs.close();}catch(Exception e){System.out.println(e);}
			try{ps.close();}catch(Exception e){System.out.println(e);}
			try{conn.close();}catch(Exception e){System.out.println(e);}
		}
	}
	
	public Collection<NegociacaoBean> listaNegociacao(){
		PreparedStatement ps=null;
		ResultSet rs=null;
		Connection conn = ServiceLocator.getConnection();
		try{
			String sql = "Select * from negociacao";
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			Collection<NegociacaoBean> negocios = new ArrayList<NegociacaoBean>();
			NegociacaoBean neg;
			NegociacaoElementoModel nem = new NegociacaoElementoModel();
			while(rs.next()){
				neg=new NegociacaoBean();
				neg.setCliente(rs.getString("NEGO_DS_CLIENTE"));
				neg.setDataAtualizacao(new Data(rs.getDate("NEGO_DT_ATUALIZACAO")));
				neg.setDataCadastro(new Data(rs.getDate("NEGO_DT_CADASTRO")));
				neg.setIdNegociacao(rs.getInt("NEGO_CD_ID"));
				neg.setNegociacao(rs.getString("NEGO_DS_NEGOCIACAO"));
				neg.setNegociador(rs.getString("NEGO_DS_NEGOCIADOR"));
				neg.setTipoNegociacao(rs.getInt("NEGO_CD_TIPO_NEGOCIACAO"));
				neg.setVariacao(rs.getDouble("NEGO_NR_VARIACAO"));
				neg.setIdUsuario(rs.getInt("USUA_CD_ID"));
				neg.setIdUsuarioContraparte(rs.getInt("USUA_CD_ID_CONTRAPARTE"));
				neg.setPasso(rs.getInt("NEGO_CD_PASSO"));
				neg.setIdModeloNegociacao(rs.getInt("MONE_CD_ID"));				
				neg.setElementos(nem.getNegociacaoElemento(rs.getInt("NEGO_CD_ID")));
				negocios.add(neg);
			}
			return negocios;
		}catch(Exception e){
			System.out.println(e);
			return null;
		}finally{
			try{rs.close();}catch(Exception e){System.out.println(e);}
			try{ps.close();}catch(Exception e){System.out.println(e);}
			try{conn.close();}catch(Exception e){System.out.println(e);}
		}
		
	}
	public boolean removeNegociacao(int idNegocio){
		PreparedStatement ps=null;
		Connection conn = ServiceLocator.getConnection();
		try{
			String sql = "delete from negociacao_elemento where nego_cd_id = ?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, idNegocio);
			ps.executeUpdate();
			
			sql = "delete from negociacao where nego_cd_id = ?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, idNegocio);
			ps.executeUpdate();
			conn.commit();
			
			return true;
		}catch(Exception e){
			System.out.println(e);
			return false;
		}finally{
			try{ps.close();}catch(Exception e){System.out.println(e);}
			try{conn.close();}catch(Exception e){System.out.println(e);}
		}
		
	}
	
	public boolean remocaoLogicaNegociacao(int idNegocio){
		PreparedStatement ps = null;
		Connection conn = ServiceLocator.getConnection();
		try{
			String sql = "UPDATE negociacao SET nego_fg_ativo = False WHERE nego_cd_id = ?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, idNegocio);
			ps.executeUpdate();
			
			conn.commit();
			
			return true;
		}
		catch(Exception e){
			System.out.println(e);
			return false;
		}finally{
			try{ps.close();}catch(Exception e){System.out.println(e);}
			try{conn.close();}catch(Exception e){System.out.println(e);}
		}
	}
		
	public Collection<NegociacaoBean> listaNegociosPorUsuario(Integer idUsuario){
		PreparedStatement ps=null;
		ResultSet rs=null;
		Connection conn = ServiceLocator.getConnection();
		try{
			String sql = "Select * from negociacao where USUA_CD_ID="+idUsuario.intValue()+" and nego_fg_ativo = True order by nego_dt_cadastro desc";
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			Collection<NegociacaoBean> negocios = new ArrayList<NegociacaoBean>();
			NegociacaoBean neg;
			NegociacaoElementoModel nem = new NegociacaoElementoModel();
			while(rs.next()){
				neg=new NegociacaoBean();
				neg.setCliente(rs.getString("NEGO_DS_CLIENTE"));
				neg.setDataAtualizacao(new Data(rs.getDate("NEGO_DT_ATUALIZACAO")));
				neg.setDataCadastro(new Data(rs.getDate("NEGO_DT_CADASTRO")));
				neg.setIdNegociacao(rs.getInt("NEGO_CD_ID"));
				neg.setNegociacao(rs.getString("NEGO_DS_NEGOCIACAO"));
				neg.setNegociador(rs.getString("NEGO_DS_NEGOCIADOR"));
				neg.setTipoNegociacao(rs.getInt("NEGO_CD_TIPO_NEGOCIACAO"));
				neg.setVariacao(rs.getDouble("NEGO_NR_VARIACAO"));
				neg.setIdUsuario(rs.getInt("USUA_CD_ID"));
				neg.setIdUsuarioContraparte(rs.getInt("USUA_CD_ID_CONTRAPARTE"));
				neg.setPasso(rs.getInt("NEGO_CD_PASSO"));
				neg.setIdModeloNegociacao(rs.getInt("MONE_CD_ID"));
				neg.setElementos(nem.getNegociacaoElemento(rs.getInt("NEGO_CD_ID")));
				negocios.add(neg);
			}
			return negocios;
		}catch(Exception e){
			System.out.println(e);
			return null;
		}finally{
			try{rs.close();}catch(Exception e){System.out.println(e);}
			try{ps.close();}catch(Exception e){System.out.println(e);}
			try{conn.close();}catch(Exception e){System.out.println(e);}
		}
		
	}
	
	public boolean atualizaPasso(int idNegociacao, int passo){
		ResultSet rs = null;
		PreparedStatement ps = null;
		Connection conn = ServiceLocator.getConnection();
		try {
			String sql = "SELECT NEGO_CD_PASSO FROM NEGOCIACAO WHERE NEGO_CD_ID=?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, idNegociacao);
			rs = ps.executeQuery();
			int passoBD = 1;
			if (rs.next()){
				passoBD=rs.getInt("NEGO_CD_PASSO");
			}
			if (passo>passoBD){
				sql = "UPDATE NEGOCIACAO SET " +
						"NEGO_CD_PASSO=? " +
						"WHERE NEGO_CD_ID=?";
				ps = conn.prepareStatement(sql);
				ps.setInt(1,passo);
				ps.setInt(2, idNegociacao);
				ps.executeUpdate();
				conn.commit();
			}
			return true;
		} catch (Exception e) {
			System.out.println(e);
			return false;
		}finally{
			try{rs.close();}catch(Exception e){System.out.println(e);}
			try{ps.close();}catch(Exception e){System.out.println(e);}
			try{conn.close();}catch(Exception e){System.out.println(e);}
		}
	}
	public Hashtable<Integer,Integer> getContagemElementos(int idNegocio) {
		PreparedStatement ps=null;
		ResultSet rs=null;
		Connection conn = ServiceLocator.getConnection();
		try{
			String sql = "select max(length(neel_tx_observacao)) as tamanho from negociacao_elemento where nego_cd_id=?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1,idNegocio);
			rs = ps.executeQuery();
			int tam = -1;
			if (rs.next()){
				tam = rs.getInt("tamanho");
			}
			if (tam<0 || tam>3000){
				tam = 3000;
			}
			
			Hashtable<Integer,Integer> s = new Hashtable<Integer,Integer>();
			//String sql = "select length(neel_tx_observacao) as cont,elne_cd_id from negociacao_elemento where nego_cd_id = "+idNegocio+" and rownum<=10 order by elne_cd_id";
			sql="SELECT ne.elne_cd_id,"+
				"length(ne.neel_tx_observacao) as cont,"+
				"ne.neel_tx_observacao,"+
				"en.elne_ds_elemento,"+
				"en.elne_tx_ontologia "+
				"from negociacao_elemento ne left join elemento_negociacao en on ne.elne_cd_id = en.elne_cd_id "+ 
				"where ne.nego_cd_id=? "+ 
				"order by elne_cd_id";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, idNegocio);
			rs = ps.executeQuery();
			while (rs.next()){
				
				s.put(new Integer(rs.getInt("elne_cd_id")), new Integer(rs.getInt("cont")));
			}
			return s;
		}catch(Exception e){
			System.out.println(e);
			return null;
		}
	}
	
}
